﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace MoneyClass
{
    public class Bank
    {
        public static DataTable GetAll()
        {
            string sql = "select [guid],[BankName] from [QT_Basic_Bank] order by [BankName] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static int Add(string name)
        {
            if (Exsits(null, name)) return -2;

            string sql = "insert into [QT_Basic_Bank]([guid],[BankName]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + name + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Update(string guid, string name)
        {
            if (Exsits(guid, name)) return -2;

            string sql = "update [QT_Basic_Bank] set [BankName]='" + name + "' where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Delete(string guid)
        {
            string sql = "delete from [QT_Basic_Bank] where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static bool Exsits(string guid, string name)
        {
            string sql = "select [guid] from [QT_Basic_Bank] where [BankName]='" + name + "'" + (string.IsNullOrEmpty(guid) ? "" : (" and [guid]<>'" + guid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }
    }

    public class UserBank
    {
        public static DataTable GetAll()
        {
            string sql = "select a.[guid],a.[ident],a.[bid],b.[BankName],a.[AccountNo],iif(a.[isdef]=true,\"是\",\"\") as status,isdef from [QT_User_Bank] a left join [QT_Basic_Bank] b on a.[bid]=b.[guid] order by a.[uid],a.[bid],a.[accountno] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static DataTable Get(string uid)
        {
            string sql = "select a.[guid],a.[ident],a.[bid],b.[BankName],a.[AccountNo],iif(a.[isdef]=true,\"是\",\"\") as status,isdef from [QT_User_Bank] a left join [QT_Basic_Bank] b on a.[bid]=b.[guid] where a.[uid]='" + uid + "' order by a.[isdef],a.[uid],a.[bid],a.[accountno] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static int Add(string uid,string ident, string bid, string account, string isdef)
        {
            if (Exsits(null, uid, ident, account)) return -2;
            if (isdef.ToLower() == "true") ClearDef(uid);

            string sql = "insert into [QT_User_Bank]([guid],[Ident],[uid],[bid],[accountno],[isdef]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + ident + "','" + uid + "','" + bid + "','" + account + "'," + isdef + ")";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Update(string guid, string uid, string ident, string bid, string account, string isdef)
        {
            if (Exsits(guid, uid, ident, account)) return -2;
            if (isdef.ToLower() == "true") ClearDef(uid);

            string sql = "update [QT_User_Bank] set [ident]='" + ident + "',[bid]='" + bid + "',[accountno]='" + account + "',[isdef]=" + isdef + " where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Delete(string guid)
        {
            string sql = "delete from [QT_User_Bank] where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int ClearDef(string uid)
        {
            string sql = "update [QT_User_Bank] set [isdef]=false where [uid]='" + uid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static bool Exsits(string guid,string uid, string ident, string account)
        {
            string sql = "select [guid] from [QT_User_Bank] where [uid]='"+uid+"' and ([ident]='" + ident + "' or [accountno]='" + account + "')" + (string.IsNullOrEmpty(guid) ? "" : (" and [guid]<>'" + guid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            return false;
        }
    }
}
